
# -*- coding: utf-8 -*- 

#目录下多excel文件的合并功能
#从目录读出所有的xls 文件， 获取第七行，保存到另外一个文件中。 

import xlrd
import xlwt
import os,sys
from os import walk


list =[]
reload(sys)
sys.setdefaultencoding('utf-8')


def getfiles():
    mypath='/Users/hansen/work/py/tongji/' 

    f = []
    for (dirpath, dirnames, filenames) in walk(mypath):
        f.extend(filenames)
        break
    print f 

    # return listfile

def getAllFiles():
    mypath='/Users/hansen/work/py/tongji/txl'
    dir = mypath
    print '----------- no sub dir'  
  
    files = os.listdir(dir)  
    for f in files:  
        print dir + os.sep + f  
  
    print '----------- all dir'  
    filelist=[] 
    for root, dirs, files in os.walk(dir):  
        for name in files:  
            print os.path.join(root, name)  
            filelist.append(os.path.join(root, name))
    return filelist


def read_xls_file(filename): 
    print "===read xls"+filename
    data =xlrd.open_workbook(filename)
    # sheet 2，4 3宣讲团情况
       # /0，7
    # sheets()[0] first sheet   table.nrows  行数
    table =data.sheets()[0]
    for i in range(table.nrows):
    	if i>=2:
            cc=table.row_values(i);
            filename = filename.decode('utf-8')
            cc.append(filename) 
            list.append(cc)


def write_data(list):
    wbk=xlwt.Workbook()
    # sheetname='1汇总表'
    sheetname='txl'
    sheet=wbk.add_sheet(sheetname.decode('utf-8'))

    for a in range(len(list)):
        for b in range(len(list[a])):
            sheet.write(a,b,list[a][b])
            print (a,b),list[a][b]

    wbk.save(sheetname.decode('utf-8')+'.xls')


if __name__ == '__main__':
        # namelist=getfiles()
        namelist=getAllFiles()
        for item in namelist :
            print "file name:"+item
            read_xls_file(item)

        write_data(list)



